JBoss Community Archive (Read Only)

Teiid 8.10

BNF for SQL Grammar

Reserved Keywords

Keyword

Usage

ADD

add set option

ALL

standard aggregate function, function, query expression body, query term
select clause, quantified comparison predicate

ALTER

alter, alter column options, alter options

AND

between predicate, boolean term

ANY

standard aggregate function, quantified comparison predicate

ARRAY_AGG

ordered aggreate function

AS

alter, array table, create procedure, option namespace
create table, create trigger, derived column, dynamic data statement
function, loop statement, xml namespace element, object table
select derived column, table subquery, text table, table name
with list element, xml serialize, xml table

ASC

sort specification

ATOMIC

compound statement, for each row trigger action

BEGIN

compound statement, for each row trigger action

BETWEEN

between predicate

BIGDECIMAL

simple data type

BIGINT

simple data type

BIGINTEGER

simple data type

BLOB

simple data type, xml serialize

BOOLEAN

simple data type

BOTH

function

BREAK

branching statement

BY

group by clause, order by clause, window specification

BYTE

simple data type

CALL

callable statement, call statement

CASE

case expression, searched case expression

CAST

function

CHAR

function, simple data type

CLOB

simple data type, xml serialize

COLUMN

alter column options

CONSTRAINT

create table body

CONTINUE

branching statement

CONVERT

function

CREATE

create procedure, create foreign temp table, create table, create temporary table
create trigger, procedure body definition

CROSS

cross join

DATE

simple data type

DAY

function

DECIMAL

simple data type

DECLARE

declare statement

DEFAULT

table element, xml namespace element, object table column, procedure parameter
xml table column

DELETE

alter, create trigger, delete statement

DESC

sort specification

DISTINCT

standard aggregate function, function, query expression body, query term
select clause

DOUBLE

simple data type

DROP

drop option, drop table

EACH

for each row trigger action

ELSE

case expression, if statement, searched case expression

END

case expression, compound statement, for each row trigger action, searched case expression

ERROR

raise error statement

ESCAPE

match predicate, text table

EXCEPT

query expression body

EXEC

dynamic data statement, call statement

EXECUTE

dynamic data statement, call statement

EXISTS

exists predicate

FALSE

non numeric literal

FETCH

fetch clause

FILTER

filter clause

FLOAT

simple data type

FOR

for each row trigger action, function, text aggreate function, text table column
xml table column

FOREIGN

alter options, create procedure, create foreign temp table, create table
foreign key

FROM

delete statement, from clause, function

FULL

qualified table

FUNCTION

create procedure

GLOBAL

create table

GROUP

group by clause

HAVING

having clause

HOUR

function

IF

if statement

IMMEDIATE

dynamic data statement

IN

procedure parameter, in predicate

INNER

qualified table

INOUT

procedure parameter

INSERT

alter, create trigger, function, insert statement

INTEGER

simple data type

INTERSECT

query term

INTO

dynamic data statement, insert statement, into clause

IS

is null predicate

JOIN

cross join, make dep options, qualified table

LANGUAGE

object table

LATERAL

table subquery

LEADING

function

LEAVE

branching statement

LEFT

function, qualified table

LIKE

match predicate

LIKE_REGEX

like regex predicate

LIMIT

limit clause

LOCAL

create foreign temp table, create temporary table

LONG

simple data type

LOOP

loop statement

MAKEDEP

option clause, table primary

MAKENOTDEP

option clause, table primary

MERGE

insert statement

MINUTE

function

MONTH

function

NO

xml namespace element, text table column, text table

NOCACHE

option clause

NOT

between predicate, compound statement, table element, is null predicate
match predicate, boolean factor, procedure parameter, procedure result column
like regex predicate, in predicate, temporary table element

NULL

table element, is null predicate, non numeric literal, procedure parameter
procedure result column, temporary table element, xml query

OBJECT

simple data type

OF

alter, create trigger

OFFSET

limit clause

ON

alter, create foreign temp table, create trigger, loop statement
qualified table, xml query

ONLY

fetch clause

OPTION

option clause

OPTIONS

alter options list, options clause

OR

boolean value expression

ORDER

order by clause

OUT

procedure parameter

OUTER

qualified table

OVER

window specification

PARAMETER

alter column options

PARTITION

window specification

PRIMARY

table element, create temporary table, primary key

PROCEDURE

alter, alter options, create procedure, procedure body definition

REAL

simple data type

REFERENCES

foreign key

RETURN

assignment statement, return statement, data statement

RETURNS

create procedure

RIGHT

function, qualified table

ROLLUP

group by clause

ROW

fetch clause, for each row trigger action, limit clause, text table

ROWS

fetch clause, limit clause

SECOND

function

SELECT

select clause

SET

add set option, option namespace, update statement

SHORT

simple data type

SIMILAR

match predicate

SMALLINT

simple data type

SOME

standard aggregate function, quantified comparison predicate

SQLEXCEPTION

sql exception

SQLSTATE

sql exception

SQLWARNING

raise statement

STRING

dynamic data statement, simple data type, xml serialize

TABLE

alter options, create procedure, create foreign temp table, create table
create temporary table, drop table, query primary, table subquery

TEMPORARY

create foreign temp table, create table, create temporary table

THEN

case expression, searched case expression

TIME

simple data type

TIMESTAMP

simple data type

TINYINT

simple data type

TO

match predicate

TRAILING

function

TRANSLATE

function

TRIGGER

alter, create trigger

TRUE

non numeric literal

UNION

cross join, query expression body

UNIQUE

other constraints, table element

UNKNOWN

non numeric literal

UPDATE

alter, create trigger, dynamic data statement, update statement

USER

function

USING

dynamic data statement

VALUES

query primary

VARBINARY

simple data type, xml serialize

VARCHAR

simple data type, xml serialize

VIRTUAL

alter options, create procedure, create table, procedure body definition

WHEN

case expression, searched case expression

WHERE

filter clause, where clause

WHILE

while statement

WITH

assignment statement, query expression, data statement

WITHOUT

assignment statement, data statement

XML

simple data type

XMLAGG

ordered aggreate function

XMLATTRIBUTES

xml attributes

XMLCOMMENT

function

XMLCONCAT

function

XMLELEMENT

xml element

XMLFOREST

xml forest

XMLNAMESPACES

xml namespaces

XMLPARSE

xml parse

XMLPI

function

XMLQUERY

xml query

XMLSERIALIZE

xml serialize

XMLTABLE

xml table

YEAR

function

Reserved Keywords

Keyword

Usage

ACCESSPATTERN

other constraints, non-reserved identifier

ARRAYTABLE

array table, non-reserved identifier

AUTO_INCREMENT

table element, non-reserved identifier

AVG

standard aggregate function, non-reserved identifier

CHAIN

sql exception, non-reserved identifier

COLUMNS

array table, non-reserved identifier, object table, text table
xml table

CONTENT

non-reserved identifier, xml parse, xml serialize

COUNT

standard aggregate function, non-reserved identifier

DELIMITER

non-reserved identifier, text aggreate function, text table

DENSE_RANK

analytic aggregate function, non-reserved identifier

DISABLED

alter, non-reserved identifier

DOCUMENT

non-reserved identifier, xml parse, xml serialize

EMPTY

non-reserved identifier, xml query

ENABLED

alter, non-reserved identifier

ENCODING

non-reserved identifier, text aggreate function, xml serialize

EVERY

standard aggregate function, non-reserved identifier

EXCEPTION

compound statement, declare statement, non-reserved identifier

EXCLUDING

non-reserved identifier, xml serialize

EXTRACT

function, non-reserved identifier

FIRST

fetch clause, non-reserved identifier, sort specification

HEADER

non-reserved identifier, text aggreate function, text table

INCLUDING

non-reserved identifier, xml serialize

INDEX

other constraints, table element, non-reserved identifier

INSTEAD

alter, create trigger, non-reserved identifier

JSONARRAY_AGG

non-reserved identifier, ordered aggreate function

JSONOBJECT

json object, non-reserved identifier

KEY

table element, create temporary table, foreign key, non-reserved identifier
primary key

LAST

non-reserved identifier, sort specification

MAX

standard aggregate function, make dep options, non-reserved identifier

MIN

standard aggregate function, non-reserved identifier

NAME

function, non-reserved identifier, xml element

NAMESPACE

option namespace, non-reserved identifier

NEXT

fetch clause, non-reserved identifier

NULLS

non-reserved identifier, sort specification

OBJECTTABLE

non-reserved identifier, object table

ORDINALITY

non-reserved identifier, text table column, xml table column

PASSING

non-reserved identifier, object table, xml query, xml table

PATH

non-reserved identifier, xml table column

QUERYSTRING

non-reserved identifier, querystring function

QUOTE

non-reserved identifier, text aggreate function, text table

RAISE

non-reserved identifier, raise statement

RANK

analytic aggregate function, non-reserved identifier

RESULT

non-reserved identifier, procedure parameter

ROW_NUMBER

analytic aggregate function, non-reserved identifier

SELECTOR

non-reserved identifier, text table column, text table

SERIAL

table element, non-reserved identifier, temporary table element

SKIP

non-reserved identifier, text table

SQL_TSI_DAY

time interval, non-reserved identifier

SQL_TSI_FRAC_SECOND

time interval, non-reserved identifier

SQL_TSI_HOUR

time interval, non-reserved identifier

SQL_TSI_MINUTE

time interval, non-reserved identifier

SQL_TSI_MONTH

time interval, non-reserved identifier

SQL_TSI_QUARTER

time interval, non-reserved identifier

SQL_TSI_SECOND

time interval, non-reserved identifier

SQL_TSI_WEEK

time interval, non-reserved identifier

SQL_TSI_YEAR

time interval, non-reserved identifier

STDDEV_POP

standard aggregate function, non-reserved identifier

STDDEV_SAMP

standard aggregate function, non-reserved identifier

SUBSTRING

function, non-reserved identifier

SUM

standard aggregate function, non-reserved identifier

TEXTAGG

non-reserved identifier, text aggreate function

TEXTTABLE

non-reserved identifier, text table

TIMESTAMPADD

function, non-reserved identifier

TIMESTAMPDIFF

function, non-reserved identifier

TO_BYTES

function, non-reserved identifier

TO_CHARS

function, non-reserved identifier

TRIM

function, non-reserved identifier, text table column, text table

VARIADIC

non-reserved identifier, procedure parameter

VAR_POP

standard aggregate function, non-reserved identifier

VAR_SAMP

standard aggregate function, non-reserved identifier

VERSION

non-reserved identifier, xml serialize

VIEW

alter, alter options, create table, non-reserved identifier

WELLFORMED

non-reserved identifier, xml parse

WIDTH

non-reserved identifier, text table column

XMLDECLARATION

non-reserved identifier, xml serialize

Reserved Keywords For Future Use

ALLOCATE

ARE

ARRAY

ASENSITIVE

ASYMETRIC

AUTHORIZATION

BINARY

CALLED

CASCADED

CHARACTER

CHECK

CLOSE

COLLATE

COMMIT

CONNECT

CORRESPONDING

CRITERIA

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_USER

CURSOR

CYCLE

DATALINK

DEALLOCATE

DEC

DEREF

DESCRIBE

DETERMINISTIC

DISCONNECT

DLNEWCOPY

DLPREVIOUSCOPY

DLURLCOMPLETE

DLURLCOMPLETEONLY

DLURLCOMPLETEWRITE

DLURLPATH

DLURLPATHONLY

DLURLPATHWRITE

DLURLSCHEME

DLURLSERVER

DLVALUE

DYNAMIC

ELEMENT

EXTERNAL

FREE

GET

GRANT

HAS

HOLD

IDENTITY

IMPORT

INDICATOR

INPUT

INSENSITIVE

INT

INTERVAL

ISOLATION

LARGE

LOCALTIME

LOCALTIMESTAMP

MATCH

MEMBER

METHOD

MODIFIES

MODULE

MULTISET

NATIONAL

NATURAL

NCHAR

NCLOB

NEW

NONE

NUMERIC

OLD

OPEN

OUTPUT

OVERLAPS

PRECISION

PREPARE

RANGE

READS

RECURSIVE

REFERENCING

RELEASE

REVOKE

ROLLBACK

SAVEPOINT

SCROLL

SEARCH

SENSITIVE

SESSION_USER

SPECIFIC

SPECIFICTYPE

SQL

START

STATIC

SUBMULTILIST

SYMETRIC

SYSTEM

SYSTEM_USER

TIMEZONE_HOUR

TIMEZONE_MINUTE

TRANSLATION

TREAT

VALUE

VARYING

WHENEVER

WINDOW

WITHIN

XMLBINARY

XMLCAST

XMLDOCUMENT

XMLEXISTS

XMLITERATE

XMLTEXT

XMLVALIDATE

Tokens

Name

Definition

Usage

all in group identifier

<identifier> <period> <star>

all in group

binary string literal

"X" | "x" "\'" (<hexit> <hexit>)+ "\'"

non numeric literal

colon

":"

make dep options, statement

comma

","

alter options list, column list, create procedure, typed element list
create table body, create temporary table, derived column list, sql exception
named parameter list, expression list, from clause, function
limit clause, nested expression, object table, option clause
options clause, order by clause, simple data type, query expression
query primary, querystring function, select clause, set clause list
in predicate, text aggreate function, text table, xml attributes
xml element, xml forest, xml namespaces, xml query
xml table

concat_op

"||"

common value expression

decimal numeric literal

(<digit>)* <period> <unsigned integer literal>

unsigned numeric literal

digit

["0"-"9"]

dollar

"$"

parameter reference

eq

"="

assignment statement, callable statement, declare statement, named parameter list
comparison operator, set clause list

escaped function

"{" "fn"

unsigned value expression primary

escaped join

"{" "oj"

table reference

escaped type

"{" ("d" | "t" | "ts" | "b")

non numeric literal

approximate numeric literal

<digit> <period> <unsigned integer literal> ["e","E"] (<plus> | <minus>)? <unsigned integer literal>

unsigned numeric literal

ge

">="

comparison operator

gt

">"

named parameter list, comparison operator

hexit

["a"-"f","A"-"F"] | <digit>

identifier

<quoted_id> (<period> <quoted_id>)*

identifier, unsigned value expression primary

id_part

("@" | "#" | <letter>) (<letter> | "_" | <digit>)*

lbrace

"{"

callable statement, match predicate

le

"<="

comparison operator

letter

["a"-"z","A"-"Z"] | ["\u0153"-"\ufffd"]

lparen

"("

standard aggregate function, alter options list, analytic aggregate function, array table
callable statement, column list, other constraints, create procedure
create table body, create temporary table, filter clause, function
group by clause, if statement, json object, loop statement
make dep options, nested expression, object table, options clause
ordered aggreate function, simple data type, query primary, querystring function
in predicate, call statement, subquery, table subquery
table primary, text aggreate function, text table, while statement
window specification, with list element, xml attributes, xml element
xml forest, xml namespaces, xml parse, xml query
xml serialize, xml table

lsbrace

"["

data type, value expression primary

lt

"<"

comparison operator

minus

"-"

plus or minus

ne

"<>"

comparison operator

ne2

"!="

comparison operator

period

"."

plus

"+"

plus or minus

qmark

"?"

callable statement, parameter reference

quoted_id

<id_part> | "\"" ("\"\"" | ~["\""])+ "\""

rbrace

"}"

callable statement, match predicate, non numeric literal, table reference
unsigned value expression primary

rparen

")"

standard aggregate function, alter options list, analytic aggregate function, array table
callable statement, column list, other constraints, create procedure
create table body, create temporary table, filter clause, function
group by clause, if statement, json object, loop statement
make dep options, nested expression, object table, options clause
ordered aggreate function, simple data type, query primary, querystring function
in predicate, call statement, subquery, table subquery
table primary, text aggreate function, text table, while statement
window specification, with list element, xml attributes, xml element
xml forest, xml namespaces, xml parse, xml query
xml serialize, xml table

rsbrace

"]"

data type, value expression primary

semicolon

";"

ddl statement, delimited statement

slash

"/"

star or slash

star

"*"

standard aggregate function, dynamic data statement, select clause, star or slash

string literal

("N" | "E")? "\'" ("\'\'" | ~["\'"])* "\'"

string

unsigned integer literal

(<digit>)+

unsigned integer, unsigned numeric literal

Production Cross-Reference

Name

Usage

add set option

alter options list

standard aggregate function

unescapedFunction

all in group

select sublist

alter

directly executable statement

alter column options

alter options

alter options list

alter column options, alter options

alter options

ddl statement

analytic aggregate function

unescapedFunction

array table

table primary

assignment statement

delimited statement

assignment statement operand

assignment statement, declare statement

between predicate

boolean primary

boolean primary

filter clause, boolean factor

branching statement

delimited statement

case expression

unsigned value expression primary

character

match predicate, text aggreate function, text table

column list

other constraints, create temporary table, foreign key, insert statement
primary key, with list element

common value expression

between predicate, boolean primary, comparison predicate, sql exception
match predicate, like regex predicate, in predicate, text table

comparison predicate

boolean primary

boolean term

boolean value expression

boolean value expression

condition

compound statement

statement, directly executable statement

other constraints

create table body

table element

create table body

create procedure

ddl statement

typed element list

array table, dynamic data statement

create foreign temp table

directly executable statement

option namespace

ddl statement

create table

ddl statement

create table body

create foreign temp table, create table

create temporary table

directly executable statement

create trigger

ddl statement, directly executable statement

condition

expression, having clause, if statement, qualified table
searched case expression, where clause, while statement

cross join

joined table

declare statement

delimited statement

delete statement

assignment statement operand, directly executable statement

delimited statement

statement

derived column

derived column list, object table, querystring function, text aggreate function
xml attributes, xml query, xml table

derived column list

json object, xml forest

drop option

alter options list

drop table

directly executable statement

dynamic data statement

data statement

raise error statement

delimited statement

sql exception

assignment statement operand, exception reference

exception reference

sql exception, raise statement

named parameter list

callable statement, call statement

exists predicate

boolean primary

expression

standard aggregate function, assignment statement operand, case expression, derived column
dynamic data statement, raise error statement, named parameter list, expression list
function, nested expression, object table column, ordered aggreate function
querystring function, return statement, searched case expression, select derived column
set clause list, sort key, xml table column, xml element
xml parse, xml serialize

expression list

callable statement, other constraints, function, group by clause
query primary, call statement, window specification

fetch clause

limit clause

filter clause

function, unescapedFunction

for each row trigger action

alter, create trigger

foreign key

create table body

from clause

query

function

unescapedFunction, unsigned value expression primary

group by clause

query

having clause

query

identifier

alter, alter column options, alter options, array table
assignment statement, branching statement, callable statement, column list
compound statement, table element, create procedure, typed element list
create foreign temp table, option namespace, create table, create table body
create temporary table, create trigger, declare statement, delete statement
derived column, drop option, drop table, dynamic data statement
exception reference, named parameter list, foreign key, function
insert statement, into clause, loop statement, xml namespace element
object table column, object table, option clause, option pair
procedure parameter, procedure result column, query primary, select derived column
set clause list, statement, call statement, table subquery
temporary table element, text aggreate function, text table column, text table
table name, update statement, with list element, xml table column
xml element, xml serialize, xml table

if statement

statement

insert statement

assignment statement operand, directly executable statement

integer parameter

fetch clause, limit clause

unsigned integer

dynamic data statement, integer parameter, make dep options, parameter reference
simple data type, text table column, text table

time interval

function

into clause

query

is null predicate

boolean primary

joined table

table primary, table reference

json object

function

limit clause

query expression body

loop statement

statement

make dep options

option clause, table primary

match predicate

boolean primary

xml namespace element

xml namespaces

nested expression

unsigned value expression primary

non numeric literal

option pair, value expression primary

non-reserved identifier

identifier, unsigned value expression primary

boolean factor

boolean term

object table column

object table

object table

table primary

comparison operator

comparison predicate, quantified comparison predicate

option clause

callable statement, delete statement, insert statement, query expression body
call statement, update statement

option pair

add set option, options clause

options clause

table element, create procedure, create table, create table body
procedure parameter, procedure result column

order by clause

function, ordered aggreate function, query expression body, text aggreate function
window specification

ordered aggreate function

unescapedFunction

parameter reference

unsigned value expression primary

data type

table element, create procedure, typed element list, declare statement
function, object table column, procedure parameter, procedure result column
temporary table element, text table column, xml table column

simple data type

data type

numeric value expression

common value expression, value expression primary

plus or minus

option pair, numeric value expression, value expression primary

primary key

create table body

procedure parameter

create procedure

procedure result column

create procedure

qualified table

joined table

query

query primary

query expression

alter, assignment statement operand, create table, insert statement
loop statement, subquery, table subquery, directly executable statement
with list element

query expression body

query expression, query primary

query primary

query term

querystring function

function

query term

query expression body

raise statement

delimited statement

like regex predicate

boolean primary

return statement

delimited statement

searched case expression

unsigned value expression primary

select clause

query

select derived column

select sublist

select sublist

select clause

set clause list

dynamic data statement, update statement

in predicate

boolean primary

sort key

sort specification

sort specification

order by clause

data statement

delimited statement

statement

alter, compound statement, create procedure, for each row trigger action
if statement, loop statement, procedure body definition, while statement

call statement

assignment statement, subquery, table subquery, directly executable statement

string

character, table element, option namespace, function
xml namespace element, non numeric literal, object table column, object table
procedure parameter, text table column, text table, xml table column
xml query, xml serialize, xml table

subquery

exists predicate, in predicate, quantified comparison predicate, unsigned value expression primary

quantified comparison predicate

boolean primary

table subquery

table primary

temporary table element

create temporary table

table primary

cross join, joined table

table reference

from clause, qualified table

text aggreate function

unescapedFunction

text table column

text table

text table

table primary

term

numeric value expression

star or slash

term

table name

table primary

unescapedFunction

unsigned value expression primary

unsigned numeric literal

option pair, value expression primary

unsigned value expression primary

integer parameter, value expression primary

update statement

assignment statement operand, directly executable statement

directly executable statement

data statement

value expression primary

array table, term

where clause

delete statement, query, update statement

while statement

statement

window specification

unescapedFunction

with list element

query expression

xml attributes

xml element

xml table column

xml table

xml element

function

xml forest

function

xml namespaces

xml element, xml forest, xml query, xml table

xml parse

function

xml query

function

xml serialize

function

xml table

table primary

Productions

string ::=

A string literal value. Use '' to escape ' in the string.

Example:

'a string'
'it''s a string'

identifier ::=

Partial or full name of a single entity.

Example:

tbl.col
"tbl"."col"

create trigger ::=

Creates a trigger action on the given target.

Example:

CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END

alter ::=

Alter the given target.

Example:

ALTER VIEW vw AS SELECT col FROM tbl

for each row trigger action ::=

Defines an action to perform on each row.

Example:

FOR EACH ROW BEGIN ATOMIC ... END

directly executable statement ::=

A statement that can be executed at runtime.

Example:

SELECT * FROM tbl

drop table ::=

Creates a trigger action on the given target.

Example:

CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END

create temporary table ::=

Creates a temporary table.

Example:

CREATE LOCAL TEMPORARY TABLE tmp (col integer)

temporary table element ::=

Defines a temporary table column.

Example:

col string NOT NULL

raise error statement ::=

Raises an error with the given message.

Example:

ERROR 'something went wrong'

raise statement ::=

Raises an error or warning with the given message.

Example:

RAISE SQLEXCEPTION 'something went wrong'

exception reference ::=

a reference to an exception

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

sql exception ::=

creates a sql exception or warning with the specified message, state, and code

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

statement ::=

A procedure statement.

Example:

IF (x = 5) BEGIN ... END

delimited statement ::=

A procedure statement terminated by ;.

Example:

SELECT * FROM tbl;

compound statement ::=

A procedure statement block contained in BEGIN END.

Example:

BEGIN NOT ATOMIC ... END

branching statement ::=

A procedure branching control statement, which typically specifies a label to return control to.

Example:

BREAK x

return statement ::=

A return statement.

Example:

RETURN 1

while statement ::=

A procedure while statement that executes until its condition is false.

Example:

WHILE (var) BEGIN ... END

loop statement ::=

A procedure loop statement that executes over the given cursor.

Example:

LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END

if statement ::=

A procedure loop statement that executes over the given cursor.

Example:

IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END

declare statement ::=

A procedure declaration statement that creates a variable and optionally assigns a value.

Example:

DECLARE STRING x = 'a'

assignment statement ::=

Assigns a variable a value in a procedure.

Example:

x = 'b'

assignment statement operand ::=

A value or command that can be used in an assignment.

All assigments except for expression are deprecated.

data statement ::=

A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.

procedure body definition ::=

Defines a procedure body on a Procedure metadata object.

Example:

BEGIN ... END

dynamic data statement ::=

A procedure statement that can execute arbitrary sql.

Example:

EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp

set clause list ::=

A list of value assignments.

Example:

col1 = 'x', col2 = 'y' ...

typed element list ::=

A list of typed elements.

Example:

col1 string, col2 integer ...

callable statement ::=

A callable statement defined using JDBC escape syntax.

Example:

{? = CALL proc}

call statement ::=

Executes the procedure with the given parameters.

Example:

CALL proc('a', 1)

named parameter list ::=

A list of named parameters.

Example:

param1 => 'x', param2 => 1

insert statement ::=

Inserts values into the given target.

Example:

INSERT INTO tbl (col1, col2) VALUES ('a', 1)

expression list ::=

A list of expressions.

Example:

col1, 'a', ...

update statement ::=

Update values in the given target.

Example:

UPDATE tbl SET (col1 = 'a') WHERE col2 = 1

delete statement ::=

Delete rows from the given target.

Example:

DELETE FROM tbl WHERE col2 = 1

query expression ::=

A declarative query for data.

Example:

SELECT * FROM tbl WHERE col2 = 1

with list element ::=

A query expression for use in the enclosing query.

Example:

X (Y, Z) AS (SELECT 1, 2)

query expression body ::=

The body of a query expression, which can optionally be ordered and limited.

Example:

SELECT * FROM tbl ORDER BY col1 LIMIT 1

query term ::=

Used to establish INTERSECT precedence.

Example:

SELECT * FROM tbl
SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2

query primary ::=

A declarative source of rows.

Example:

TABLE tbl
SELECT * FROM tbl1

query ::=

A SELECT query.

Example:

SELECT col1, max(col2) FROM tbl GROUP BY col1

into clause ::=

Used to direct the query into a table.

This is deprecated. Use INSERT INTO with a query expression instead.

Example:

INTO tbl

select clause ::=

The columns returned by a query. Can optionally be distinct.

Example:

SELECT *
SELECT DISTINCT a, b, c

select sublist ::=

An element in the select clause

Example:

tbl.*
tbl.col AS x

select derived column ::=

A select clause item that selects a single column.

This is slightly different than a derived column in that the AS keyword is optional.

Example:

tbl.col AS x

derived column ::=

An optionally named expression.

Example:

tbl.col AS x

all in group ::=

A select sublist that can select all columns from the given group.

Example:

tbl.*

ordered aggreate function ::=

An aggregate function that can optionally be ordered.

Example:

XMLAGG(col1) ORDER BY col2
ARRAY_AGG(col1)

text aggreate function ::=

An aggregate function for creating separated value clobs.

Example:

TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)

standard aggregate function ::=

A standard aggregate function.

Example:

COUNT(*)

analytic aggregate function ::=

An analytic aggregate function.

Example:

ROW_NUMBER()

filter clause ::=

An aggregate filter clause applied prior to accumulating the value.

Example:

FILTER (WHERE col1='a')

from clause ::=

A query from clause containing a list of table references.

Example:

FROM a, b
FROM a right outer join b, c, d join e".</p>

table reference ::=

An optionally escaped joined table.

Example:

a
a inner join b

joined table ::=

A table or join.

Example:

a
a inner join b

cross join ::=

A cross join.

Example:

a CROSS JOIN b

qualified table ::=

An INNER or OUTER join.

Example:

a inner join b

table primary ::=

A single source of rows.

Example:

a

make dep options ::=

options for the make dep hint

Example:

(min:10000)

xml serialize ::=

Serializes an XML value.

Example:

XMLSERIALIZE(col1 AS CLOB)

array table ::=

The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.

Example:

ARRAYTABLE (col1 COLUMNS x STRING) AS y

text table ::=

The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.

Example:

TEXTTABLE (file COLUMNS x STRING) AS y

text table column ::=

A text table column.

Example:

x INTEGER WIDTH 6

xml query ::=

Executes an XQuery to return an XML result.

Example:

XMLQUERY('<a>...</a>' PASSING doc)

object table ::=

Returns table results by processing a script.

Example:

OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X

object table column ::=

object table column.

Example:

y integer 'teiid_row_number'

xml table ::=

Returns table results by processing an XQuery.

Example:

XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X

xml table column ::=

XML table column.

Example:

y FOR ORDINALITY

unsigned integer ::=

An unsigned interger value.

Example:

12345

table subquery ::=

A table defined by a subquery.

Example:

(SELECT * FROM tbl) AS x

table name ::=

A table named in the FROM clause.

Example:

tbl AS x

where clause ::=

Specifies a search condition

Example:

WHERE x = 'a'

condition ::=

A boolean expression.

boolean value expression ::=

An optionally ORed boolean expression.

boolean term ::=

An optional ANDed boolean factor.

boolean factor ::=

A boolean factor.

Example:

NOT x = 'a'

boolean primary ::=

A boolean predicate or simple expression.

Example:

col LIKE 'a%'

comparison operator ::=

A comparison operator.

Example:

=

comparison predicate ::=

A value comparison.

Example:

= 'a'

subquery ::=

A subquery.

Example:

(SELECT * FROM tbl)

quantified comparison predicate ::=

A subquery comparison.

Example:

= ANY (SELECT col FROM tbl)

match predicate ::=

Matches based upon a pattern.

Example:

LIKE 'a_'

like regex predicate ::=

A regular expression match.

Example:

LIKE_REGEX 'a.*b'

character ::=

A single character.

Example:

'a'

between predicate ::=

A comparison between two values.

Example:

BETWEEN 1 AND 5

is null predicate ::=

A null test.

Example:

IS NOT NULL

in predicate ::=

A comparison with multiple values.

Example:

IN (1, 5)

exists predicate ::=

A test if rows exist.

Example:

EXISTS (SELECT col FROM tbl)

group by clause ::=

Defines the grouping columns

Example:

GROUP BY col1, col2

having clause ::=

Search condition applied after grouping.

Example:

HAVING max(col1) = 5

order by clause ::=

Specifices row ordering.

Example:

ORDER BY x, y DESC

sort specification ::=

Defines how to sort on a particular expression

Example:

col1 NULLS FIRST

sort key ::=

A sort expression.

Example:

col1

integer parameter ::=

A literal integer or parameter reference to an integer.

Example:

?

limit clause ::=

Limits and/or offsets the resultant rows.

Example:

LIMIT 2

fetch clause ::=

ANSI limit.

Example:

FETCH FIRST 1 ROWS ONLY

option clause ::=

Specifies query options.

Example:

OPTION MAKEDEP tbl

expression ::=

A value.

Example:

col1

common value expression ::=

Establishes the precedence of concat.

Example:

'a' || 'b'

numeric value expression ::=

Example:

1 + 2

plus or minus ::=

The + or - operator.

Example:

+

term ::=

A numeric term

Example:

1 * 2

star or slash ::=

The * or / operator.

Example:

/

value expression primary ::=

A simple value expression.

Example:

+col1

parameter reference ::=

A parameter reference to be bound later.

Example:

?

nested expression ::=

An expression nested in parens

Example:

(1)

unsigned value expression primary ::=

An unsigned simple value expression.

Example:

col1

window specification ::=

The window specification for an analytical or windowed aggregate function.

Example:

OVER (PARTION BY col1)

case expression ::=

If/then/else chain using a common search predicand.

Example:

CASE col1 WHEN 'a' THEN 1 ELSE 2

searched case expression ::=

If/then/else chain using multiple search conditions.

Example:

CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2

xml parse ::=

Parses the given value as XML.

Example:

XMLPARSE(DOCUMENT doc WELLFORMED)

querystring function ::=

Produces a URL query string from the given arguments.

Example:

QUERYSTRING('path', col1 AS opt, col2 AS val)

xml element ::=

Creates an XML element.

Example:

XMLELEMENT(NAME "root", child)

xml attributes ::=

Creates attributes for the containing element.

Example:

XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)

json object ::=

Produces a JSON object containing name value pairs.

Example:

JSONOBJECT(col1 AS val1, col2 AS val2)

derived column list ::=

a list of name value pairs

Example:

col1 AS val1, col2 AS val2

xml forest ::=

Produces an element for each derived column.

Example:

XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)

xml namespaces ::=

Defines XML namespace URI/prefix combinations

Example:

XMLNAMESPACES('http://foo' AS foo)

xml namespace element ::=

An xml namespace

Example:

NO DEFAULT

data type ::=

A data type.

Example:

STRING[]

non numeric literal ::=

An escaped or simple non numeric literal.

Example:

'a'

unsigned numeric literal ::=

An unsigned numeric literal value.

Example:

1.234

ddl statement ::=

A data definition statement.

Example:

CREATE FOREIGN TABLE X (Y STRING)

option namespace ::=

A namespace used to shorten the full name of an option key.

Example:

SET NAMESPACE 'http://foo' AS foo

create procedure ::=

Defines a procedure or function invocation.

Example:

CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING

procedure parameter ::=

A procedure or function parameter

Example:

OUT x INTEGER

procedure result column ::=

A procedure result column.

Example:

x INTEGER

create table ::=

Defines a table or view.

Example:

CREATE VIEW vw AS SELECT 1

create foreign temp table ::=

Defines a foreign temp table

Example:

CREATE FOREIGN TEMPORARY TABLE t (x string) ON z

create table body ::=

Defines a table.

Example:

(x string) OPTIONS (CARDINALITY 100)

foreign key ::=

Defines the foreign key referential constraint.

Example:

FOREIGN KEY (a, b) REFERENCES tbl (x, y)

primary key ::=

Defines the primary key.

Example:

PRIMARY KEY (a, b)

other constraints ::=

Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.

Example:

UNIQUE (a)

column list ::=

A list of column names.

Example:

(a, b)

table element ::=

Defines a table column.

Example:

x INTEGER NOT NULL

options clause ::=

A list of statement options.

Example:

OPTIONS ('x' 'y', 'a' 'b')

option pair ::=

An option key/value pair.

Example:

'key' 'value'

alter options ::=

alters options of tables/procedure

Example:

ALTER FOREIGN TABLE foo OPTIONS (ADD cardinality 100)

alter options list ::=

a list of alterations to options

Example:

OPTIONS (ADD updatable true)

drop option ::=

drop option

Example:

DROP updatable

add set option ::=

add or set an option pair

Example:

ADD updatable true

alter column options ::=

alters a set of column options

Example:

ALTER COLUMN bar OPTIONS (ADD updatable true)
JBoss.org Content Archive (Read Only), exported from JBoss Community Documentation Editor at 2020-03-13 12:16:18 UTC, last content change 2014-12-02 14:18:19 UTC.